﻿using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class C_OilCardRechargeDAL : BaseDAL<C_OilCardRecharge>
    {
        public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;
        #region

        public object GetRechargeList(int page, int rows, string start, string end, string center, string station, string cardCode,
                                      string invoiceNumber, E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            List<RechargeExt> list = new List<RechargeExt>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select distinct identity(int,1,1) as 行号,编码=ocr.ID,卡号=ocr.CardCode,司机工号=ocr.DriverWorkID,司机姓名=ocr.DriverName,充值金额=Convert(decimal(18,2),ocr.RechargeAmount),
                        备注=ocr.Remark,卡内余额=Convert(decimal(18,2),ocr.Remaining),填写人=ocr.FillPerson,填写时间=convert(varchar(10),ocr.FillTime, 120),分中心编码=ocr.CenterID,
                        充值时间=ocr.RechargeTime,分中心=tc.名称,分站编码=ocr.StationID,分站=ts.名称,卡内金额=Convert(decimal(18,2),ocr.CardAmount),发票编号=ocr.FPCode
                        into #tempa                   
                        from C_OilCardRecharge ocr 
                        ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ocr.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ocr.CenterID=tc.编码 ");
            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ocr.RechargeTime", Convert.ToDateTime(start), sb);
            WhereClauseUtility.AddDateTimeLessThan("ocr.RechargeTime", Convert.ToDateTime(end), sb);
            WhereClauseUtility.AddStringEqual("ocr.CardCode", cardCode, sb);
            WhereClauseUtility.AddStringLike("ocr.FPCode", invoiceNumber, sb);
            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddStringEqual("ocr.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("ocr.StationID", station, sb);
                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("ocr.CenterID", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ocr.StationID", station, sb);
                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("ocr.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("ocr.StationID", selfStationID, sb);

                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("ocr.CenterID", center, sb);
                    WhereClauseUtility.AddStringEqual("ocr.StationID", station, sb);
                    WhereClauseUtility.AddStringEqual("ocr.DriverWorkID", selfWorkCode, sb);

                }
                sb.Append(" order by 充值时间 desc ");
                sb.Append(@" select * into #temp from #tempa where 1=1 ");
                sb.Append(@" select top " + rows + " A.*  from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
                sb.Append(@"  SELECT count(*) FROM #temp t   drop table #tempa,#temp ");

                DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        RechargeExt info = new RechargeExt();
                        info.CenterID = dr["分中心编码"].ToString();
                        info.StationID = dr["分站编码"].ToString();
                        info.ID = Convert.ToInt32(dr["编码"]);
                        info.CardCode = dr["卡号"].ToString();
                        info.CardAmount = dr["卡内金额"].ToString();
                        info.DriverWorkID = dr["司机工号"].ToString();
                        info.DriverName = dr["司机姓名"].ToString();
                        info.Remaining = dr["卡内余额"].ToString();
                        info.Remark = dr["备注"].ToString();
                        info.FillPerson = dr["填写人"].ToString();
                        info.FillTime = dr["填写时间"].ToString();
                        info.RechargeTime = dr["充值时间"].ToString();
                        info.RechargeAmount = dr["充值金额"].ToString();
                        info.Center = dr["分中心"].ToString();
                        info.Station = dr["分站"].ToString();
                        info.FPCode = dr["发票编号"].ToString();
                        list.Add(info);
                    }
                }
                int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                var result = new { total = total, rows = list };
                return result;
            }
        }
        #endregion
        //通过卡号取对应的司机工号，姓名，分中心，分站，卡内余额
        public C_OilCardLink GetOilCardLinkList(string cardCode)
        {
            List<C_OilCardLink> list = new List<C_OilCardLink>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 司机工号=DriverWorkID,司机姓名=DriverName,分中心=tc.名称,分站=ts.名称
                               ,卡内余额=case when ISNUMERIC(oci.bak1)=0 then 0 else Convert(decimal(18,2),oci.bak1) end
                               ,分中心编码=oci.CenterID,分站编码=oci.StationID
                        from C_OilCardInfo oci ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            //sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TPerson tp on tp.工号=oci.DriverWorkID ");
            //sb.Append(" left join  P_User  pu  on pu.WorkCode = oci.DriverWorkID ");
            //sb.Append(" left join  P_Department  pd  on pd.ID = pu.DepID ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ts.编码=oci.StationID ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on tc.编码=oci.CenterID  ");
            sb.Append(" where oci.CardCode='" + cardCode + "' and oci.IsActive=1  ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardLink info = new C_OilCardLink();
                if (dr.Read())
                {
                    info.DriverWorkID = dr["司机工号"].ToString();
                    info.DriverName = dr["司机姓名"].ToString();
                    info.Center = dr["分中心"].ToString();
                    info.Station = dr["分站"].ToString();
                    info.CenterID = Convert.ToInt32(dr["分中心编码"]);
                    info.StationID = dr["分站编码"].ToString();
                    info.bak1 = dr["卡内余额"].ToString();
                    list.Add(info);


                }
                return info;
            }
        }
        public C_OilCardInfo GetOilCardList(string cardCode)
        {
            List<C_OilCardInfo> list = new List<C_OilCardInfo>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select 编码=ID,卡号=CardCode,司机工号=DriverWorkID,司机姓名=DriverName
                               ,卡内金额=case when ISNUMERIC(bak1)=0 then 0 else Convert(decimal(18,2),bak1) end
                               ,是否有效=IsActive,备注=Remark,填写时间=FillTime,填写人=FillPerson                        
                        from C_OilCardInfo  ");
            sb.Append(" where CardCode='" + cardCode + "' and IsActive=1 ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardInfo info = new C_OilCardInfo();
                if (dr.Read())
                {
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.CardCode = dr["卡号"].ToString();
                    info.bak1 = Convert.ToString(dr["卡内金额"]);
                    info.DriverWorkID = dr["司机工号"].ToString();
                    info.DriverName = dr["司机姓名"].ToString();
                    info.IsActive = Convert.ToBoolean(dr["是否有效"]);
                    info.FillTime = Convert.ToDateTime(dr["填写时间"]);
                    info.FillPerson = dr["填写人"].ToString();
                    info.Remark = dr["备注"].ToString();
                    list.Add(info);
                }
                return info;
            }
        }

        //通过id取出原来的加油数据
        public C_OilCardRefuel GetOldModel(int id)
        {
            List<C_OilCardRefuel> list = new List<C_OilCardRefuel>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select  编码=ocr.ID,卡号=ocr.CardCode,司机工号=ocr.DriverWorkID,司机姓名=ocr.DriverName,加油金额=Convert(decimal(18,2),ocr.RefuelMoney),
                                备注=ocr.Remark,车牌号码=ocr.ActualCode,填写人=ocr.FillPerson,填写时间=ocr.FillTime,分中心编码=ocr.CenterID,
                                加油时间=ocr.RefuelTime,分中心=tc.名称,分站编码=ocr.StationID,分站=ts.名称,车辆编号=ocr.AmbulanceNumber,班次=ocr.Shift,
                                加油油号=ocr.OilType,加油量=Convert(decimal(18,2),ocr.OilMass),上次加油路码=Convert(decimal(18,2),ocr.LastGasPathCode),
                                本次加油路码=Convert(decimal(18,2),ocr.GasPathCode),行驶公里=Convert(decimal(18,2),ocr.DrivingKM),车辆编码=ocr.AmbulanceCode,
                                加油人=ocr.RefuelPerson,凭证编号=ocr.ProofNumber,卡内金额=ocr.bakup1,卡内余额=ocr.bakup2                       
                        from C_OilCardRefuel ocr  ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ocr.StationID=ts.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ocr.CenterID=tc.编码 ");
            sb.Append(" where ID='" + id + "' ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardRefuel info = new C_OilCardRefuel();
                if (dr.Read())
                {
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.CardCode = dr["卡号"].ToString();
                    info.DriverWorkID = dr["司机工号"].ToString();
                    info.DriverName = dr["司机姓名"].ToString();
                    info.RefuelMoney = Convert.ToDecimal(dr["加油金额"]);
                    info.ActualCode = dr["车牌号码"].ToString();
                    info.AmbulanceCode = dr["车辆编码"].ToString();
                    info.AmbulanceNumber = dr["车辆编号"].ToString();
                    info.CenterID = Convert.ToInt32(dr["分中心编码"]);
                    info.StationID = dr["分站编码"].ToString();
                    info.RefuelTime = Convert.ToDateTime(dr["加油时间"]);
                    info.RefuelPerson = dr["加油人"].ToString();
                    info.Shift = dr["班次"].ToString();
                    info.OilType = dr["加油油号"].ToString();
                    info.OilMass = Convert.ToDouble(dr["加油金额"]);
                    info.FillTime = Convert.ToDateTime(dr["填写时间"]);
                    info.LastGasPathCode = Convert.ToDouble(dr["上次加油路码"]);
                    info.GasPathCode =  Convert.ToDouble(dr["本次加油路码"]);
                    info.DrivingKM = Convert.ToDouble(dr["行驶公里"]);
                    info.ProofNumber = dr["凭证编号"].ToString();
                    info.bakup1 = dr["卡内金额"].ToString();
                    info.bakup2 = dr["卡内余额"].ToString();
                    info.FillPerson = dr["填写人"].ToString();
                    info.Remark = dr["备注"].ToString();
                    list.Add(info);
                }
                return info;
            }
        }
      
        //通过id取出原来的充值数据
        public C_OilCardRecharge GetOldRechargeModel(int id)
        {
            List<C_OilCardRecharge> list = new List<C_OilCardRecharge>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select  编码=ocr.ID,卡号=ocr.CardCode,司机工号=ocr.DriverWorkID,司机姓名=ocr.DriverName,
                                充值金额=Convert(decimal(18,2),ocr.RechargeAmount),充值时间=ocr.RechargeTime,
                                备注=ocr.Remark,填写人=ocr.FillPerson,填写时间=ocr.FillTime,分中心编码=ocr.CenterID,
                                分站编码=ocr.StationID,发票=ocr.FPCode,卡内金额=Convert(decimal(18,2),ocr.CardAmount),
                                卡内余额=Convert(decimal(18,2),ocr.Remaining)                                                     
                        from C_OilCardRecharge ocr  ");
            sb.Append(" where ID='" + id + "' ");
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
            {
                C_OilCardRecharge info = new C_OilCardRecharge();
                if (dr.Read())
                {
                    info.ID = Convert.ToInt32(dr["编码"]);
                    info.CardCode = dr["卡号"].ToString();
                    info.DriverWorkID = dr["司机工号"].ToString();
                    info.DriverName = dr["司机姓名"].ToString();
                    info.RechargeAmount = Convert.ToDecimal(dr["充值金额"]);
                    info.CenterID = Convert.ToInt32(dr["分中心编码"]);
                    info.StationID = dr["分站编码"].ToString();
                    info.RechargeTime = Convert.ToDateTime(dr["充值时间"]);
                    info.FillPerson = dr["填写人"].ToString();
                    info.FillTime = Convert.ToDateTime(dr["填写时间"]);
                    info.FPCode = dr["发票"].ToString();
                    info.CardAmount = Convert.ToDecimal(dr["卡内金额"]);
                    info.Remaining = Convert.ToDecimal(dr["卡内余额"]);
                    info.Remark = dr["备注"].ToString();
                    list.Add(info);
                }
                return info;
            }
        }
    }
}
